insert overwrite table jms_dm.dm_center_stop_time_detail_dt partition ( dt )
 --36
select
to_date(t.arrival_time) as date_time,
waybill_no,
send_shipment_no,
arrival_site as center_name,
arrival_site_code as center_name_code,
arrival_time as center_arrival_time,
send_time as center_send_time,
last_time as center_last_time,
send_next_name,
case when send_next_type=2 then '是' else '否' end next_type,
case when send_next_type=2 then (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600
     else (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600 end diff_time,
null as invalid_type,
1 as stop_type,
     dt
from jms_dm.dm_center_stop_time_dt t
where t.send_time <> '0000-0000-0000' and t.arrival_time < t.send_time  and t.dt='{{ execution_date | date_add(-2) | cst_ds }}'
and (
        case when send_next_type=2 then (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600
             else (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600 end >=36
     and
 case when send_next_type=2 then (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600
             else (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600 end  < 48
)

union all
--48
 select
    to_date(t.arrival_time) as date_time,
    waybill_no,
    send_shipment_no,
    arrival_site as center_name,
    arrival_site_code as center_name_code,
    arrival_time as center_arrival_time,
    send_time as center_send_time,
    last_time as center_last_time,
    send_next_name,
    case when send_next_type=2 then '是' else '否' end next_type,
    case when send_next_type=2 then (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600
         else (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600 end diff_time,
    null as invalid_type,
    2 as stop_type , 
    t.dt
    from jms_dm.dm_center_stop_time_dt t
    where t.send_time <> '0000-0000-0000' and t.arrival_time < t.send_time  and t.dt='{{ execution_date | date_add(-2) | cst_ds }}'
    and ( case when send_next_type=2 then (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600
         else (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600 end >=48
       )
--无效
union all 
select
    dt as date_time,
    waybill_no,
    send_shipment_no,
    arrival_site as center_name,
    arrival_site_code as center_name_code,
    arrival_time as center_arrival_time,
    send_time as center_send_time,
    last_time as center_last_time,
    send_next_name,
    case when send_next_type=2 then '是' else '否' end next_type,
    null as diff_time,
    case when arrival_time is null then '有发无到'
         when send_time is null then '有到无发'
         when (case when send_next_type=2 then (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600
                 else (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) end  < 0) then  '先发后到'
         when (case when send_next_type=2 then (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600
                 else (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) end  is null)
              and last_time is null then  '发车时间为空'
                 end as invalid_type,
    3 as stop_type , 
    dt
    from jms_dm.dm_center_stop_time_dt t
    where  t.dt='{{ execution_date | date_add(-2) | cst_ds }}'
    and ( arrival_time is null  or send_time is null or
          (case when send_next_type=2 then (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600
                 else (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) end  < 0) or

          (case when send_next_type=2 then (unix_timestamp(last_time) - unix_timestamp(nvl(cancal_time,arrival_time))) / 3600
                 else (unix_timestamp(send_time) - unix_timestamp(nvl(cancal_time,arrival_time))) end  is null )
         )

;
